package junior.db.dao.base;

import junior.db.dao.enums.Operator;
import junior.db.dao.query.AbstractQuery;
import junior.db.dao.query.WithQuery;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.util.StringUtils;

import java.util.List;
import java.util.function.BiFunction;

/**
 * @author wubo
 * @date 2024/9/3 15:12
 */
public class FilterSqlHelper {
    protected final static String WHERE = "where";
    protected final static String ON = "on";
    
    protected static String doQuery(AbstractQuery param, String path, BiFunction<String, Object, String> onValueFunc) {
        StringBuilder buff = new StringBuilder();
        if (param.getCustomFirst() != null) {
            buff.append(param.getCustomFirst());
        }
        if (param instanceof WithQuery) {
            List<WithQuery.WithQueryNode> nodeList = ((WithQuery) param).getNodeList();
            if (CollectionUtils.isNotEmpty(nodeList)) {
                buff.append("with ");
                if (((WithQuery) param).isRecursive()) {
                    buff.append(" recursive ");
                }
                int index = 0;
                for (WithQuery.WithQueryNode node : nodeList) {
                    if (index > 0) {
                        buff.append(", ");
                    }
                    buff.append(node.getCte()).append(" as (");
                    buff.append(doQuery(node.getQuery(), getPath(path) + "nodeList["+index+"].query", onValueFunc));
                    buff.append(")");
                    index ++;
                }
                buff.append(" ");
            }
        }
        buff.append(getSelects(param, path, onValueFunc));
        if (param instanceof SubQuery) {
            buff.append(" from ").append(getSubQuery((SubQuery) param, path, true, onValueFunc));
        } else {
            if (param.getTable() != null) {
                buff.append(" from ").append(getTable(param));
            }
        }
        buff.append(getJoinQuery(param, path, onValueFunc));
        buff.append(getFilters(param, WHERE, path, onValueFunc));
        if (param.getGroupBies() != null) {
            buff.append(getGroupBies(param));
        }
        if (param.getHavings() != null) {
            buff.append(getHavings(param));
        }
        buff.append(getUnions(param, path, onValueFunc));
        doOrderBy(param.getOrderBy(), buff);
        buff.append(getLimit(param.getLimit()));
        if (param.getCustomLast() != null) {
            buff.append(param.getCustomLast());
        }
        return buff.toString();
    }
    
    protected static String doSubQuery(SubQuery param, String path, BiFunction<String, Object, String> onValueFunc) {
        StringBuilder buff = new StringBuilder();
        if (param.getCustomFirst() != null) {
            buff.append(param.getCustomFirst());
        }
        buff.append(getSelects(param, "", onValueFunc));
        buff.append(" from (").append(doQuery(param.getQuery(), getPath(path) + "query", onValueFunc)).append(") ");
        if (param.getAlias() != null) {
            buff.append(param.getAlias());
        }
        buff.append(getFilters(param, WHERE, path, onValueFunc));
        if (param.getGroupBies() != null) {
            buff.append(getGroupBies(param));
        }
        if (param.getHavings() != null) {
            buff.append(getHavings(param));
        }
        buff.append(getUnions(param, path, onValueFunc));
        doOrderBy(param.getOrderBy(), buff);
        buff.append(getLimit(param.getLimit()));
        if (param.getCustomLast() != null) {
            buff.append(param.getCustomLast());
        }
        return buff.toString();
    }
    
    protected static String getSelects(AbstractQuery param, String path, BiFunction<String, Object, String> onValueFunc) {
        StringBuilder buff = new StringBuilder();
        buff.append("select ");
        int idx = 0;
        for (Object selectObject : param.getFinalSelectFields()) {
            if (idx > 0) {
                buff.append(", ");
            }
            AbstractQuery.ISelectItem select = (AbstractQuery.ISelectItem) selectObject;
            if (select.getType() == AbstractQuery.SelectItemType.Field) {
                buff.append(param.getSelectColumn(select));
            } else if (select.getType() == AbstractQuery.SelectItemType.Expression) {
                int selectItemIndex = idx;
                
                buff.append(param.getSelectExpression(select, f -> {
                    Object val = ((Expression) select).getValues().get(f);
                    String valuePath = getPath(path) + "finalSelectFields[" + selectItemIndex + "].values[" + f + "]";
                    if (onValueFunc != null) {
                        return onValueFunc.apply(valuePath, val);
                    } else {
                        return "#{" + valuePath + "}";
                    }
                }));
                if (StringUtils.hasText(select.getAlias())) {
                    buff.append(" as ").append(select.getAlias());
                }
            }
            idx++;
        }
        return buff.toString();
    }
    
    protected static String getTable(AbstractQuery param) {
        StringBuilder buff = new StringBuilder();
        buff.append(param.getTable());
        if (param.getPrefix() != null) {
            buff.append(" ").append(param.getPrefix());
        }
        return buff.toString();
    }
    
    protected static String getSubQuery(SubQuery param, String path, boolean requireOuterAlias, BiFunction<String, Object, String> onValueFunc) {
        StringBuilder buff = new StringBuilder();
        buff.append("(").append(doQuery(param.getQuery(), getPath(path) + "query", onValueFunc)).append(")");
        if (param.getAlias() != null && requireOuterAlias) {
            buff.append(" ").append(param.getAlias());
        }
        return buff.toString();
    }
    
    protected static String getJoinQuery(AbstractQuery param, String path, BiFunction<String, Object, String> onValueFunc) {
        StringBuilder buff = new StringBuilder();
        if (param.getJoinList() != null && !param.getJoinList().isEmpty()) {
            int idx = 0;
            for (Object joinObject : param.getJoinList()) {
                JoinQuery joinQuery = (JoinQuery) joinObject;
                buff.append(" ").append(joinQuery.getJoin()).append(" ");
                AbstractQuery jq = joinQuery.getJoinQuery();
                if (jq instanceof SubQuery) {
                    buff.append(getSubQuery((SubQuery) jq, getPath(path) + "joinList[" + idx + "].joinQuery", true, onValueFunc));
                } else {
                    buff.append(getTable(jq));
                }
                buff.append(getFilters(joinQuery.getJoinQuery(), ON, getPath(path) + "joinList[" + idx + "].joinQuery", onValueFunc));
                idx++;
            }
        }
        return buff.toString();
    }
    
    protected static String getFilters(AbstractFilter param, String filterWord, String path, BiFunction<String, Object, String> onValueFunc) {
        StringBuilder buff = new StringBuilder();
        List<IFilterItem> filters = param.getFilters();
        if (filters != null && filters.size() > 0) {
            buff.append(" ").append(filterWord).append(" ");
            for (int i = 0; i < filters.size(); i++) {
                IFilterItem f = filters.get(i);
                if (f instanceof SubQueryFilter) {
                    buff.append(getSubQueryFilter((SubQueryFilter) f, getPath(path) + "filters[" + i + "]", onValueFunc));
                } else {
                    Filter filter = (Filter) f;
                    if (filter.isLogic()) {
                        buff.append(filter.getOperator().getOptString());
                    } else {
                        buff.append(filter.getLeft()).append(filter.getOperator().getOptString());
                        if (filter.getOperator() == Operator.NULL || filter.getOperator() == Operator.NOT_NULL) {
                            continue;
                        }
                        if (filter.getRightIsExpression()) {
                            SqlValue sqlValue = filter.getRightExpression();
                            for (int j = 0; j < sqlValue.getList().size(); j++) {
                                SqlValue.Part part = sqlValue.getList().get(j);
                                if (part.isArg()) {
                                    Object val = part.getVal();
                                    String valuePath = getPath(path) + "filters[" + i + "].rightExpression.list[" + j + "].val";
                                    if (onValueFunc != null) {
                                        buff.append(onValueFunc.apply(valuePath, val));
                                    } else {
                                        buff.append(val == null ? "null" : "#{" + valuePath + ",javaType=" + val.getClass().getTypeName() + "}");
                                    }
                                } else {
                                    buff.append(part.getVal());
                                }
                            }
                        } else if (filter.getOperator() == Operator.BT) {
                            Object[] array = (Object[]) filter.getRight();
                            String valuePath = getPath(path) + "filters[" + i + "].right";
                            buff.append(array[0] == null ? "null" : "#{" + valuePath + "[" + 0 + "],javaType=" + array[0].getClass().getTypeName() + "}");
                            buff.append(" and ");
                            buff.append(array[1] == null ? "null" : "#{" + valuePath + "[" + 1 + "],javaType=" + array[1].getClass().getTypeName() + "}");
                        } else {
                            Object val = filter.getRight();
                            String valuePath = getPath(path) + "filters[" + i + "].right";
                            if (onValueFunc != null) {
                                buff.append(onValueFunc.apply(valuePath, val));
                            } else {
                                buff.append(val == null ? "null" : "#{" + valuePath + ",javaType=" + val.getClass().getTypeName() + "}");
                            }
                        }
                    }
                }
            }
        }
        return buff.toString();
    }
    
    protected static String getSubQueryFilter(SubQueryFilter filter, String path, BiFunction<String, Object, String> onValueFunc) {
        StringBuilder buff = new StringBuilder();
        if (filter.getFilterField() != null) {
            buff.append(filter.getFilterField());
        }
        buff.append(" ").append(filter.getOperator()).append(" ");
        buff.append(getSubQuery(filter.getSubQuery(), getPath(path) + "subQuery", false, onValueFunc));
        return buff.toString();
    }
    
    protected static String getGroupBies(AbstractQuery<?> param) {
        StringBuilder buff = new StringBuilder();
        if (param.getGroupBies().size() > 0) {
            buff.append(" group by ");
            int index = 0;
            for (String groupBy : param.getGroupBies()) {
                if (index++ > 0) {
                    buff.append(", ");
                }
                buff.append(groupBy);
            }
        }
        return buff.toString();
    }
    
    protected static String getHavings(AbstractQuery<?> param) {
        StringBuilder buff = new StringBuilder();
        if (param.getHavings().size() > 0) {
            buff.append(" having ");
            int index = 0;
            for (String having : param.getHavings()) {
                if (index++ > 0) {
                    buff.append(", ");
                }
                buff.append(having);
            }
        }
        return buff.toString();
    }
    
    protected static String getLimit(Limit limit) {
        StringBuilder buff = new StringBuilder();
        if (limit != null) {
            Integer start = limit.getStart();
            buff.append(" limit ").append(start != null && start > 0 ? start + ", " : "").append(limit.getLimit());
        }
        return buff.toString();
    }
    
    protected static String getUnions(AbstractQuery param, String path, BiFunction<String, Object, String> onValueFunc) {
        StringBuilder buff = new StringBuilder();
        if (param.getUnionQuery() != null) {
            UnionQuery unionQuery = param.getUnionQuery();
            int index = 0;
            for (UnionQuery.UnionQueryParamItem item : unionQuery.getItems()) {
                buff.append(" ").append(item.getUnion()).append(" ");
                buff.append(doQuery(item.getParam(), getPath(path) + "unionQuery.items["+index+"].param", onValueFunc));
                index ++;
            }
            doOrderBy(param.getOrderBy(), buff);
            buff.append(getLimit(param.getLimit()));
        }
        return buff.toString();
    }
    
    private static void doOrderBy(String orderBy, StringBuilder buff) {
        if (orderBy != null) {
            buff.append(" order by ").append(orderBy);
        }
    }
    
    protected static String getPath(String path) {
        if (StringUtils.hasText(path)) {
            return path + ".";
        }
        return "";
    }
}
